package edu.eece261.fall11;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class ListDBAdapter {

	// Let's be a Singleton
	private static ListDBAdapter instance = null;

	// Member Variables
	private static String DB_NAME = "ShoppingListStories.db";
	private static final String DB_TABLE = "Lists";
	private static final int DB_VERSION = 5;

	public static final String DB_KEY_ID = "_id";

	// Public Key (Column) Names for convenience
	public static final String KEY_TITLE = "Title";
	public static final String KEY_ITEMS = "Items";
	public static final String KEY_UPDATERS = "Updaters";
	public static final String KEY_TIMESTAMPS = "Timestamps";
	public static final String KEY_LAST_UPDATER = "LastUpdater";
	public static final String KEY_OWNER = "Owner";
	public static final String KEY_FINISHED = "Finished";
	public static final String KEY_GLOBAL_ID = "GlobalID";

	// Public Column numbers for convenience
	public static final int TITLE_COLUMN = 1;
	public static final int ITEMS_COLUMN = 2;
	public static final int UPDATERS_COLUMN = 3;
	public static final int TIMESTAMPS_COLUMN = 4;
	public static final int LAST_UPDATER_COLUMN = 5;
	public static final int OWNER_COLUMN = 6;
	public static final int FINISHED_COLUMN = 7;
	public static final int GLOBAL_ID_COLUMN = 8;
	
	private static final String DB_CREATE = "create table " + DB_TABLE + " ("
			+ DB_KEY_ID + " integer primary key autoincrement, " + KEY_TITLE
			+ " text, " + KEY_ITEMS + " text, " + KEY_UPDATERS + " text, "
			+ KEY_TIMESTAMPS + " text, " + KEY_LAST_UPDATER
			+ " text, " + KEY_OWNER + " text, " + KEY_FINISHED + " int, "
			+ KEY_GLOBAL_ID + " long);";

	private SQLiteDatabase db;

	private final Context context;
	private DBHelper dbHelper;

	public static final String PREFS_NAME = "SHOPPINGLISTLOGIN";

	public static ListDBAdapter getListDBAdapter(Context _context) {
		SharedPreferences settings = _context.getSharedPreferences(PREFS_NAME,
				0);
		if (instance == null
				|| (DB_NAME.equals(settings.getString("user", "") + DB_NAME))) {
			DB_NAME = settings.getString("user", "") + "ShoppingListLists.db";
			instance = new ListDBAdapter(_context);
			return instance;
		} else {
			return instance;
		}
	}

	private ListDBAdapter(Context _context) {
		context = _context;
		dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
	}

	public ListDBAdapter open() throws SQLException {
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		db.close();
	}
	
	public long insertResult(String title, String items, String updaters, String timestamps, String lastUpdater,
			String owner, int finished, long globalID) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_TITLE, title);
		contentValues.put(KEY_ITEMS, items);
		contentValues.put(KEY_UPDATERS, updaters);
		contentValues.put(KEY_TIMESTAMPS, timestamps);
		contentValues.put(KEY_LAST_UPDATER, lastUpdater);
		contentValues.put(KEY_GLOBAL_ID, globalID);
		contentValues.put(KEY_OWNER, owner);
		contentValues.put(KEY_FINISHED, finished);
		return db.insert(DB_TABLE, null, contentValues);
	}

	public long insertList(String title, String items, String updaters, String timestamps, String lastUpdater,
			String owner, int finished, long globalID) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_TITLE, title);
		contentValues.put(KEY_ITEMS, items);
		contentValues.put(KEY_UPDATERS, updaters);
		contentValues.put(KEY_TIMESTAMPS, timestamps);
		contentValues.put(KEY_LAST_UPDATER, lastUpdater);
		contentValues.put(KEY_GLOBAL_ID, globalID);
		contentValues.put(KEY_OWNER, owner);
		contentValues.put(KEY_FINISHED, finished);
		return db.insert(DB_TABLE, null, contentValues);
	}

	public boolean removeEntry(long _rowIndex) {
		return db.delete(DB_TABLE, DB_KEY_ID + "=" + _rowIndex, null) > 0;
	}

	public Cursor getAllEntries() {
		return db.query(DB_TABLE, null, null, null, null, null, null);
	}

	public Cursor getOpenLists() {
		return db.query(DB_TABLE, null, KEY_FINISHED + " = 0", null, null,
				null, null);
	}

	public Cursor getMyLists(Context _context) {
		SharedPreferences settings = _context.getSharedPreferences(PREFS_NAME,
				0);
		return db.query(DB_TABLE, null, KEY_OWNER + " = '"
				+ settings.getString("user", "") + "'", null, null, null, null);
	}

	public Cursor getFollowing(Context _context) {
		SharedPreferences settings = _context.getSharedPreferences(PREFS_NAME,
				0);
		return db.query(DB_TABLE, null, KEY_OWNER + " != '"
				+ settings.getString("user", "") + "'", null, null, null, null);
	}

	public Cursor getEntryMatchingTitle(String title) {
		return db.query(DB_TABLE, null, KEY_TITLE + " = '" + title + "'", null,
				null, null, null);
	}

	public Cursor getEntryMatchingId(long id) {
		return db.query(DB_TABLE, null, DB_KEY_ID + " = " + id, null, null,
				null, null);
	}

	public int updateEntry(int key, String title, String items, String updaters, String timestamps,
			String owner, String lastUpdater, int finished, long globalID) {
		String where = DB_KEY_ID + "=" + key;
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_TITLE, title);
		contentValues.put(KEY_ITEMS, items);
		contentValues.put(KEY_UPDATERS, updaters);
		contentValues.put(KEY_TIMESTAMPS, timestamps);
		contentValues.put(KEY_LAST_UPDATER, lastUpdater);
		contentValues.put(KEY_GLOBAL_ID, globalID);
		contentValues.put(KEY_OWNER, owner);
		contentValues.put(KEY_FINISHED, finished);
		return db.update(DB_TABLE, contentValues, where, null);
	}

	public int updateEntryFromGlobal(String title, String items, String updaters, String timestamps, 
			String owner, String lastUpdater, int finished, long globalID) {
		String where = KEY_GLOBAL_ID + " = " + globalID;
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_TITLE, title);
		contentValues.put(KEY_ITEMS, items);
		contentValues.put(KEY_UPDATERS, updaters);
		contentValues.put(KEY_TIMESTAMPS, timestamps);
		contentValues.put(KEY_LAST_UPDATER, lastUpdater);
		contentValues.put(KEY_OWNER, owner);
		contentValues.put(KEY_FINISHED, finished);
		return db.update(DB_TABLE, contentValues, where, null);
	}

	public int removeEntryFromGlobal(long globalID) {
		return db.delete(DB_TABLE, KEY_GLOBAL_ID + " = " + globalID, null);
	}

	public int updateFinished(String title, int finished) {
		String where = KEY_TITLE + "= '" + title + "'";
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_FINISHED, finished);
		return db.update(DB_TABLE, contentValues, where, null);
	}

	public boolean isOwner(String title, Context _context) {
		SharedPreferences settings = _context.getSharedPreferences(PREFS_NAME,
				0);
		Cursor c = getEntryMatchingTitle(title);
		if (c.getCount() > 0) {
			c.moveToFirst();
			if (c.getString(OWNER_COLUMN)
					.equals(settings.getString("user", ""))) {
				return true;
			}
		}
		return false;
	}

	public boolean isListFinished(String title) {
		Cursor c = getEntryMatchingTitle(title);
		if (c.getCount() > 0) {
			c.moveToFirst();
			if (c.getInt(FINISHED_COLUMN) == 1) {
				return true;
			}
		}
		return false;
	}
	
	private static class DBHelper extends SQLiteOpenHelper {
		public DBHelper(Context context, String name, CursorFactory factory,
				int version) {
			super(context, name, factory, version);
		}

		// Called when no database exists in
		// disk and the helper class needs
		// to create a new one.
		@Override
		public void onCreate(SQLiteDatabase _db) {
			_db.execSQL(DB_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion,
				int _newVersion) {
			// Log the version upgrade.
			Log.w("StoryDBAdapter", "Upgrading from version " + _oldVersion
					+ " to " + _newVersion
					+ ", which will destroy all old data");

			_db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);

			onCreate(_db);
		}
	}
}
